 /*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Business;

import Connections.Database;
import Entities.BoothEmployee;
import Entities.BoothInformation;
import Entities.BoothProduct;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
/**
 *
 * @author Administrator
 */
public class BoothManage extends Database {
    Connection conn=null;
    public BoothManage()
    {
      
    }
    public boolean  AddBooth(BoothInformation objBooth)
    {
        conn=getConn();
        try {
            PreparedStatement prsm = conn.prepareStatement("INSERT INTO BoothInformation "
                     + "("
                     + "name" 
                     + ",space" 
                     + ",spaceUnitMeasureID"
                     + ",fees"
                     + ",sponsor," 
                     + "exhibitorID,"
                     + "contractcode,"
                     + "status," 
                     +"registerDate"                   
                     + ",modifiedDate) VALUES(?,?,?,?,?,?,?,?,?,?) ");
             prsm.setString(1, objBooth.getName());
             prsm.setInt(2, objBooth.getSpace());
             prsm.setInt(3,objBooth.getSpaceUnitMeasureID());
             prsm.setDouble(4, objBooth.getFees());
             prsm.setString(5, objBooth.getSponsor());
             prsm.setInt(6, objBooth.getExhibitorID());
             prsm.setInt(7,objBooth.getContractCode());
             prsm.setInt(8, objBooth.getStatus());
             prsm.setDate(9,(java.sql.Date)  objBooth.getRegisterDate());
             prsm.setDate(10,(java.sql.Date)  objBooth.getMoDifiedDate());
             prsm.executeUpdate();
             closeConn();
             return true;
        } catch (SQLException ex) {
            //System.out.println(ex);
           //JOptionPane.showMessageDialog(null, "Insert error !");
           closeConn();
           return false;
        }
        
        
   }
   public boolean UpdateBooth(BoothInformation objBooth,int id)
    {
       conn=getConn();
        try {
            PreparedStatement prsm = conn.prepareStatement("UPDATE BoothInformation set name=?"
                    + ",space=?"
                    + ",spaceUnitMeasureID=?"
                    + ",fees=?"
                    + ",sponsor=?"
                    + ",exhibitorID=?"
                    + ",contractCode=?"
                    + ",status=?"
                    + ",modifiedDate=? where id=?");
             prsm.setString(1, objBooth.getName());
             prsm.setInt(2, objBooth.getSpace());
             prsm.setInt(3,objBooth.getSpaceUnitMeasureID());
             prsm.setDouble(4, objBooth.getFees());
             prsm.setString(5, objBooth.getSponsor());
             prsm.setInt(6, objBooth.getExhibitorID());
             prsm.setInt(7,objBooth.getContractCode());
             prsm.setInt(8, objBooth.getStatus());
             prsm.setDate(9, (Date) objBooth.getMoDifiedDate());
             prsm.setInt(10, id);
             prsm.executeUpdate();
             closeConn();
             return true;
        } catch (SQLException ex) {
            //ex.printStackTrace();
            //JOptionPane.showMessageDialog(null, "update error !");
            closeConn();
            return false;
        }

     }
 public ArrayList<BoothInformation> GetListBooth()
  {
     conn=getConn();
      ArrayList<BoothInformation> arrList=new ArrayList<BoothInformation>();
     try{
         PreparedStatement prsm=conn.prepareStatement("Select * from BoothInformation");
         ResultSet rs=prsm.executeQuery();
         while(rs.next())
         {
            BoothInformation objBooth=new BoothInformation(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getDouble(5), rs.getString(6)
                    , rs.getInt(7), rs.getInt(8), rs.getInt(9), rs.getDate(10),rs.getDate(11));
             arrList.add(objBooth);
         }
         closeConn();
     }
     catch(Exception ex)
     {
          JOptionPane.showMessageDialog(null, "GetList error !");
          closeConn();
     }
    
    return arrList;
  }
    public BoothInformation GetBoothInformationByID(int id)
    {
        //ArrayList<BoothInformation> arrList=new ArrayList<BoothInformation>();
        BoothInformation objBooth=null;
        conn=getConn();
        try {
                PreparedStatement prsm=conn.prepareStatement("Select * from BoothInformation"
                        + " where id=?");
                prsm.setInt(1, id);
                ResultSet rs=prsm.executeQuery();
                while(rs.next())
                {
                  objBooth=new BoothInformation(rs.getInt(1),rs.getString(2), rs.getInt(3),rs.getInt(4)
                          , rs.getDouble(5), rs.getString(6), rs.getInt(7), rs.getInt(8),
                          rs.getInt(9), rs.getDate(10),rs.getDate(11));
                }
                closeConn();
        } catch (Exception e) {
            //e.printStackTrace();
             JOptionPane.showMessageDialog(null, "Error !");
             closeConn();
        }
        return objBooth;

    }
   public void DeleteBooth(int id)
  {
     conn=getConn();
      try {
            PreparedStatement prsm=conn.prepareStatement("Delete from"
                    + " BoothInformation where id=?");
            prsm.setInt(1, id);
            prsm.executeUpdate();
            closeConn();
      } catch (Exception e) {
          e.printStackTrace();
          closeConn();
          JOptionPane.showMessageDialog(null, "Delete Error !");
      }

  }
//   public int getExhibitorIDByName(String name){
//        conn = getConn();
//        int id=0;
//        try {
//
//            PreparedStatement preStm = conn.prepareStatement("SELECT id FROM Exhibitor WHERE name like '%?%'");
//            preStm.setString(1, name);
//            ResultSet rs = preStm.executeQuery();
//            while(rs.next())
//            {
//               id=rs.getInt(1);
//            }
//        } catch (SQLException ex) {
//            JOptionPane.showMessageDialog(null, "getExhibitorById error !");
////            ex.printStackTrace();
//            //Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
//        }
//        return id;
//    }
    

    public ArrayList<String> getExhibitorToArrayList(){
        conn=getConn();
        ArrayList<String> arrList = new ArrayList<String>();
        try {

            PreparedStatement prsm = conn.prepareStatement("Select * from Exhibitor");
            ResultSet rs = prsm.executeQuery();
            while (rs.next()) {
                String name = rs.getInt("id")+"-"+rs.getNString("firstName") +" "+ rs.getNString("lastName");
                arrList.add(name);
            }
           closeConn();
        } catch (SQLException ex) {
            //Logger.getLogger(BoothManage.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "Get exhibitorToVector Error!");
            closeConn();
        }
        return arrList;
    }
    public String getExhibitorNameById(int id){
           conn = getConn();
       String name="";
        try {

            PreparedStatement preStm = conn.prepareStatement("SELECT firstName,lastName FROM Exhibitor WHERE id = ?");
            preStm.setInt(1, id);
            ResultSet rs = preStm.executeQuery();
            while(rs.next())
            {
               name=rs.getString("firstName")+" "+rs.getString("firstName");
            }
            closeConn();
        } catch (SQLException ex) {
              ex.printStackTrace();
            JOptionPane.showMessageDialog(null,"getExhibitorById error !");
            closeConn();
            //Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return name;
    }
    public ArrayList<BoothInformation> SearchBooth(String name)
    {
         conn=getConn();
         ArrayList<BoothInformation> arrayList=new ArrayList<BoothInformation>();//'%"+str+"%'
        try {
           PreparedStatement prsm=conn.prepareStatement("Select * from BoothInformation where name like '%"+name+"%' order by name");
           ResultSet rs=prsm.executeQuery();
           while(rs.next())
           {
//            BoothInformation objBooth=new BoothInformation(rs.getInt("id"), rs.getString("name"), rs.getInt("space")
//                      , rs.getInt("spaceUnitMeasureID"), rs.getDouble("fees"), rs.getString("sponsor"), rs.getInt("exhibitorID")
//                      , rs.getInt("contractCode"),rs.getInt("status")
//                      , rs.getDate("registerDate"),rs.getDate("modifiedDate"));
               BoothInformation objBooth=new BoothInformation();
               objBooth.setId(rs.getInt("id"));
               objBooth.setName(rs.getString("name"));
               objBooth.setSpace(rs.getInt("space"));
               objBooth.setSpaceUnitMeasureID(rs.getInt("spaceUnitMeasureID"));
               objBooth.setFees(rs.getDouble("fees"));
               objBooth.setSponsor(rs.getString("sponsor"));
               objBooth.setExhibitorID(rs.getInt("exhibitorID"));
               objBooth.setContractCode(rs.getInt("contractCode"));
               objBooth.setStatus(rs.getInt("status"));
               objBooth.setRegisterDate(rs.getDate("registerDate"));
               objBooth.setRegisterDate(rs.getDate("modifiedDate"));
               arrayList.add(objBooth);
               
           }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null,"GetData Failure !");
            Logger.getLogger(BoothInformation.class.getName()).log(Level.SEVERE, null, e);
        }

       return arrayList;
    }
    public ArrayList<BoothProduct> GetBoothProduct(int boothID)
    {
       conn=getConn();
       ArrayList<BoothProduct> arrlist=new ArrayList<BoothProduct>();
        BoothProduct boothproduct=null;
        try {
             PreparedStatement prsm=conn.prepareStatement("Select * from BoothProduct where boothID=?");
             prsm.setInt(1, boothID);
             ResultSet rs=prsm.executeQuery();
             while(rs.next())
             {
                boothproduct=new BoothProduct(rs.getInt(1), rs.getString(2),
                        rs.getFloat(3),rs.getString(4),rs.getInt(5),rs.getNString(6),
                        rs.getInt(7), rs.getInt(8), rs.getByte(9),rs.getInt(10),rs.getInt(11),
                        rs.getDate(12));
                 arrlist.add(boothproduct);

             }
             closeConn();
        } catch (Exception e) {
            e.printStackTrace();
            closeConn();
        }
       return arrlist;
    }
    public ArrayList<BoothEmployee> GetBoothEmployee(int boothID)
    {
        conn=getConn();
        ArrayList<BoothEmployee> arrList=new ArrayList<BoothEmployee>();
        BoothEmployee objEmployee=null;
        try {
              PreparedStatement prsm=conn.prepareStatement("Select * from BoothEmployee where BoothID=?");
              prsm.setInt(1, boothID);
              ResultSet rs=prsm.executeQuery();
              while(rs.next())
              {
                 objEmployee=new BoothEmployee(rs.getInt(1),
                         rs.getString(2), rs.getInt(3),rs.getString(4)
                         ,rs.getString(5), rs.getByte(6),rs.getInt(7),
                         rs.getDate(8)
                         );
                 arrList.add(objEmployee);
              }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return arrList;
    }
}
